/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.jsysacc.asistencia.model.dao.component.maestros;

import com.cerd.util.BaseSP01.JUtilBaseSP01;
import com.jsysacc.asistencia.model.beans.maestros.Ubigeo;
import com.jsysacc.asistencia.model.dao.design.maestros.IUbigeoDAO;
import com.jsysacc.asistencia.model.dao.ds.Cnx;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author Carlos
 */
public class UbigeoDAO extends JUtilBaseSP01 implements IUbigeoDAO {

    private Connection cnx = null;
    private PreparedStatement pst = null;
    private Statement stm = null;
    private ResultSet rs = null;
    private CallableStatement cs = null;

    @Override
    public Ubigeo getByPk(int pk) throws SQLException {
        Ubigeo oUbigeo = null;
        String sql = " select * from " + Cnx.getSchema() + ".sp_t_ubigeos_get_by_pk(?) ; ";
        cnx = Cnx.getConnection();
        try {
            pst = cnx.prepareStatement(sql);
            int pos = 1;
            pst.setInt(1, pk);
            rs = pst.executeQuery();
            while (rs.next()) {
                oUbigeo = new Ubigeo();
                mapearBeanForRS(rs, oUbigeo);
            }
        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (cnx != null) {
                    cnx.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex.getMessage());
            }
        }

        return oUbigeo;
    }

    @Override
    public ArrayList<Ubigeo> getDepartamentosAll(String cod_dep) throws SQLException {
        ArrayList<Ubigeo> lista = new ArrayList<>();
        Ubigeo oUbigeo = null;
        String sql = " select * from " + Cnx.getSchema() + ".sp_t_ubigeos_get_by_departamentos(?) ; ";
        cnx = Cnx.getConnection();
        try {
            pst = cnx.prepareStatement(sql);
            pst.setString(1, cod_dep);
            rs = pst.executeQuery();
            while (rs.next()) {
                oUbigeo = new Ubigeo();
                mapearBeanForRS(rs, oUbigeo);
                lista.add(oUbigeo);
            }
        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (cnx != null) {
                    cnx.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex.getMessage());
            }
        }

        return lista;
    }

    @Override
    public ArrayList<Ubigeo> getProvinciasByCodDep(String cod_dep) throws SQLException {
        ArrayList<Ubigeo> lista = new ArrayList<>();
        Ubigeo oUbigeo = null;
        String sql = " select * from " + Cnx.getSchema() + ".sp_t_ubigeos_get_by_provincias(?) ; ";
        cnx = Cnx.getConnection();
        try {
            pst = cnx.prepareStatement(sql);
            pst.setString(1, cod_dep);
            rs = pst.executeQuery();
            while (rs.next()) {
                oUbigeo = new Ubigeo();
                mapearBeanForRS(rs, oUbigeo);
                lista.add(oUbigeo);
            }
        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (cnx != null) {
                    cnx.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex.getMessage());
            }
        }

        return lista;
    }

    @Override
    public ArrayList<Ubigeo> getDistritosByCodProv(String cod_dep, String cod_prov) throws SQLException {
        ArrayList<Ubigeo> lista = new ArrayList<>();
        Ubigeo oUbigeo = null;
        String sql = " select * from " + Cnx.getSchema() + ".sp_t_ubigeos_get_by_distritos(?,?) ; ";
        cnx = Cnx.getConnection();
        try {
            pst = cnx.prepareStatement(sql);
            pst.setString(1, cod_dep);
            pst.setString(2, cod_prov);
            rs = pst.executeQuery();
            while (rs.next()) {
                oUbigeo = new Ubigeo();
                mapearBeanForRS(rs, oUbigeo);
                lista.add(oUbigeo);
            }
        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (cnx != null) {
                    cnx.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex.getMessage());
            }
        }

        return lista;
    }

    @Override
    public Ubigeo getUbigeo(String cod_dep, String cod_prov, String cod_dist) throws SQLException {

        Ubigeo oUbigeo = null;
        String sql = " select * from " + Cnx.getSchema() + ".sp_t_ubigeos_get_by_ubigeo (?, ?, ?) ; ";
        cnx = Cnx.getConnection();
        try {
            pst = cnx.prepareStatement(sql);
            int pos = 1;
            pst.setString(pos++, cod_dep);
            pst.setString(pos++, cod_prov);
            pst.setString(pos++, cod_dist);
            rs = pst.executeQuery();
            while (rs.next()) {
                oUbigeo = new Ubigeo();
                mapearBeanForRS(rs, oUbigeo);
            }
        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (cnx != null) {
                    cnx.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex.getMessage());
            }
        }

        return oUbigeo;
    }

}
